package cn.zyy.dao;

import cn.zyy.entity.Student;
import cn.zyy.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

/**
 * scoreDao类  用于持久化
 */
public class ScoreDaoImpl implements ScoreDao {
    //根据学生姓名和科目名称给学生记录成绩
    public Map<String,Integer> addScore(String stuName, String courseName) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select s.stu_id,c.course_id from student s,course c where s.stu_name = ? and c.course_name = ?";
        Map<String, Integer> map = new HashMap<String, Integer>();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,stuName);
            pstmt.setString(2,courseName);
            rs = pstmt.executeQuery();
            while(rs.next()) {
                map.put("stuId",rs.getInt(1));
                map.put("courseId",rs.getInt(2));
            }
            return map;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(rs,pstmt,conn);
        }
        return null;
    }
    public boolean isAddScore(int stuId, int courseId) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select * from score where stu_id = ? and course_id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,stuId);
            pstmt.setInt(2,courseId);
            rs = pstmt.executeQuery();
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(rs,pstmt,conn);
        }
        return false;
    }
    public int addScore(int stuId, int courseId, double score, boolean isAddScore) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        String sql = null;
        if (isAddScore) {
            sql = "update score set score = ? where stu_id = ? and course_id = ?";
        }else {
            sql = "insert into score(score,stu_id,course_id) values(?,?,?)";
        }
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(2,stuId);
            pstmt.setInt(3,courseId);
            pstmt.setDouble(1,score);
            int line = pstmt.executeUpdate();
            return line;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(null,pstmt,conn);
        }
        return 0;
    }
    //根据科目名称查询所有学生的成绩，并降序排列
    public int selectScore(String courseName) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        String sql = "select course_id from course where course_name = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,courseName);
            rs = pstmt.executeQuery();
            int courseId = 0;
            while(rs.next()){
                courseId = rs.getInt(1);
            }
            return courseId;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(rs,pstmt,conn);
        }
        return 0;
    }
    public ArrayList<Student> selectScore(int courseId) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Student> studentScores = new ArrayList<>();
        String sql = "select s.stu_name,c.score from student s RIGHT JOIN score c on s.stu_id = c.stu_id where course_id = ? order by score desc";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,courseId);
            rs = pstmt.executeQuery();
            Student student = null;
            while (rs.next()) {
                student = Student.builder().stuName(rs.getString(1)).score(rs.getDouble(2)).build();
                studentScores.add(student);
            }
            return studentScores;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeAll(rs,pstmt,conn);
        }
        return null;
    }

}
